package jdbc;

import org.junit.Test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

public class TestQuery {


    @Test
    public void testDBMetaData() {
        Connection conn = null;
        ResultSet rset = null;
        PreparedStatement stmt = null;

        try {
            conn = ConnUtil.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();

            System.out.println("数据库主版本号 " + metaData.getDatabaseMajorVersion() );
            System.out.println("数据库副版本号 " + metaData.getDatabaseMinorVersion());
            System.out.println(metaData.getDatabaseProductName());
            System.out.println(metaData.getDatabaseProductVersion());

            System.out.println("用户名 " + metaData.getUserName());
            rset = metaData.getCatalogs();
            while (rset.next()) {
                System.out.println("数据库 " + rset.getString(1) );
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnUtil.close(rset, stmt, conn);
        }
    }


    @Test
    public void query() {
        Connection conn = null;
        ResultSet rset = null;
        PreparedStatement stmt = null;

        try {
            String sql = " SELECT * FROM `jdbc`.`user` LIMIT ?, ? ";
            conn = ConnUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 0);
            stmt.setInt(2, 25);
            rset = stmt.executeQuery();

            ResultSetMetaData metaData = rset.getMetaData();

            for (int i = 1; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i);
                String columnLabel = metaData.getColumnLabel(i);
                String columnTypeName = metaData.getColumnTypeName(i);
                System.out.println(" 字段 " + columnName + " 别名 " + columnLabel + " 类型 " + columnTypeName);
            }

            while (rset.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", rset.getString("id"));
                map.put("name", rset.getString("name"));
                map.put("age", rset.getInt("age"));
                map.put("email", rset.getString("email"));
                map.put("phone", rset.getString("phone"));
                System.out.println(map);
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            ConnUtil.close(rset, stmt, conn);
        }

    }



    @Test
    public void callQueryAll() {
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rset = null;
        String sql = "CALL QueryAllById(?)";

        try {
            conn = ConnUtil.getConnection();
            stmt = conn.prepareCall(sql);
            stmt.setInt(1, 10);
            rset = stmt.executeQuery();
            while (rset.next()) {
                System.out.println(rset.getString("id"));
                System.out.println(rset.getString("name"));
                System.out.println(rset.getInt("age"));
                System.out.println(rset.getString("email"));
                System.out.println(rset.getString("phone"));
                System.out.println("--------------------");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            ConnUtil.close(rset, stmt, conn);
        }
    }


    @Test
    public void callNameById() {
        Connection conn = null;
        CallableStatement stmt = null;
        String sql = "CALL FindNameById (?, ?)";

        try {
            conn = ConnUtil.getConnection();
            stmt = conn.prepareCall(sql);

            stmt.setInt(1, 18);
            stmt.registerOutParameter(2, Types.VARCHAR);

            stmt.executeQuery();

            String s = stmt.getString(2);
            System.out.println(s);
        }
        catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        finally {
            ConnUtil.close(null, stmt, conn);
        }
    }







}



